package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    UserMapper userMapper;

    @Autowired
    WorkspaceService workspaceService;


    /*
     * 营业额统计
     * */
    @Override
    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
        //1. 获取时间区间列表：12/14  ~ 12/21
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);

        while (!begin.equals(end)) {
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        log.info("营业额统计时间区间：" + dateList);

        //2.遍历时间列表
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //统计每一天的营业额：select sum(amount) from orders where status=5 and order_time = 时间

            Map map = new HashMap();
            map.put("status", Orders.COMPLETED);
            map.put("time", date);
            Double turnover = orderMapper.sumByMap(map);
            turnoverList.add(turnover);
        }

        //3.封装查询结果数据
        return TurnoverReportVO.builder()
                //使用apache.commons工具提供的字符串数据转换的方法：json,将指定集合中的所有元素使用指定的字符串进行拼接成字符串
                .dateList(StringUtils.join(dateList, ","))      //日期列表：逗号分割
                .turnoverList(StringUtils.join(turnoverList, ","))  //营业额列表：逗号分割
                .build();
    }

    /*
     * 用户统计
     *
     * */
    @Override
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        //1. 获取时间区间列表：12/14  ~ 12/21
        LocalDate beginTime = begin;
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);

        while (!begin.equals(end)) {
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        log.info("用户统计时间区间：" + dateList);

        //2.遍历时间列表
        List<Integer> totalUserList = new ArrayList<>();//用户总量列表集合
        List<Integer> newUserList = new ArrayList<>();//新增用户列表集合
        //查询周期前一天的用户总量select count(*) from user where create_time < 2023-12-14
        Map map = new HashMap();
        map.put("time2", beginTime);
        Integer userTotal = userMapper.selectByMap(map);


        for (LocalDate date : dateList) {
            //2.1 统计每一天新增的用户数量：select count(*) from user where create_time = 2023-12-15
            map.put("time2", null);
            map.put("time", date);
            Integer count = userMapper.selectByMap(map);
            newUserList.add(count);

            //2.2统计当天的用户总量
            userTotal += count;
            totalUserList.add(userTotal);
        }

        //3.封装查询结果数据
        return UserReportVO.builder()
                .dateList(StringUtils.join(dateList, ",")) //时间列表
                .newUserList(StringUtils.join(newUserList, ","))//新增用户列表
                .totalUserList(StringUtils.join(totalUserList, ","))//用户总量列表
                .build();
    }

    /*
     * 订单统计
     *
     * */
    @Override
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        //1. 获取时间区间列表：12/14  ~ 12/21
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);

        while (!begin.equals(end)) {
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        log.info("订单统计时间区间：" + dateList);


        //2.遍历时间列表
        List<Integer> orderCountList = new ArrayList<>(); //订单总量列表
        List<Integer> validOrderCountList = new ArrayList<>(); //有效订单总量列表

        Integer totalOrderAllCount = 0;//时间区间内的订单列表
        Integer validOrderAllCount = 0;//时间内
        Double orderCompletionRate = 0.0;//时间区间的完成率

        for (LocalDate date : dateList) {
            //2.1统计每一天的订单数量，每一天的订单总量
            //2.1.1每一天的订单总量：select count(*) from orders where orders_time = ？
            Map map = new HashMap();
            map.put("time", date);
            Integer orderCount = orderMapper.countByMap(map);
            //2.1.2统计每一天的有效订单总量：select count(*) from orders where orders_time = ？and status = 5
            map.put("status", Orders.COMPLETED);
            Integer validOrderCount = orderMapper.countByMap(map);

            //2.1.3添加到集合中
            orderCountList.add(orderCount);
            validOrderCountList.add(validOrderCount);

            //3.统计指定时间区间内订单总量，有效订单总量，计算订单完成率
            totalOrderAllCount += orderCount;
            validOrderAllCount += validOrderCount;
        }

        //计算时间区间内的订单完成率
        if (totalOrderAllCount != 0) {
            orderCompletionRate = validOrderAllCount.doubleValue() / totalOrderAllCount;
        }


        //4.封装查询结果数据
        return OrderReportVO.builder()
                //使用apache.commons工具提供的字符串数据转换的方法：json,将指定集合中的所有元素使用指定的字符串进行拼接成字符串
                .dateList(StringUtils.join(dateList, ","))      //时间列表：逗号分割
                .orderCompletionRate(orderCompletionRate)//时间区间订单效率
                .orderCountList(StringUtils.join(orderCountList, ","))  //订单总量列表
                .totalOrderCount(totalOrderAllCount)//时间区间订单总量
                .validOrderCount(validOrderAllCount) //时间区间有效订单总量
                .validOrderCountList(StringUtils.join(validOrderCountList, ",")) //有效订单列表列表：逗号分割
                .build();
    }

    /*
     * 查询销量排名top10
     *
     * */
    @Override
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        //1.调用持久层，查询指定时间区间的销量排行榜单
        List<GoodsSalesDTO> goodsSalesDTOList = orderMapper.top10(begin, end);

        //2.遍历商品销量结果集合，获取是商品名称列表和商品销量列表
        List<String> nameList = new ArrayList<>(); //菜品名称列表
        List<Integer> numberList = new ArrayList<>();//菜品销量列表

        for (GoodsSalesDTO goodsSalesDTO : goodsSalesDTOList) {
            nameList.add(goodsSalesDTO.getName());
            numberList.add(goodsSalesDTO.getNumber());
        }

        //3.封装vo
        return SalesTop10ReportVO.builder()
                .nameList(StringUtils.join(nameList, ",")) //菜品名称列表
                .numberList(StringUtils.join(numberList, ","))//菜品销量列表
                .build();
    }

    /*
     *导出excel报表数据
     * */
    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        //1. 加载指定位置的Excel对象（创建excel文件）
        //类加载器：加载类路径下的资源文件
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("运营数据报表模板.xlsx");
        XSSFWorkbook excelObj = new XSSFWorkbook(inputStream);

        //2. 获取模版中的sheet对象（创建excel中的工作簿）
        XSSFSheet sheetObj = excelObj.getSheet("Sheet1");

        //3. 创建row对象（创建工作簿的行）
        //设置报表数据--设置时间
        sheetObj.getRow(1).getCell(1).setCellValue(LocalDate.now().toString());

        //设置报表数据--订单信息
        LocalDate time = LocalDate.now();
        LocalDate monthBefoeTime = time.minusDays(30);

        Double turnover = 0.0;//营业额
        Double orderCompletionRate = 0.0; //订单完成率
        Double unitPrice = 0.0; //平均客单价
        int newUsers = 0; //新增用户数
        int validOrderCount = 0; //有效订单

        for (int i = 1; i <= 30; i++) {
            BusinessDataVO businessDataVO = workspaceService.businessData(monthBefoeTime);
            //1. 获取当前要写入数据的行
            XSSFRow row = sheetObj.getRow(6 + i);
            row.getCell(1).setCellValue(monthBefoeTime.toString()); //时间
            row.getCell(2).setCellValue(businessDataVO.getTurnover()); //营业额
            row.getCell(3).setCellValue(businessDataVO.getValidOrderCount()); //有效订单
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate()); //订单完成率
            row.getCell(5).setCellValue(businessDataVO.getUnitPrice()); //平均客单价
            row.getCell(6).setCellValue(businessDataVO.getNewUsers()); //新增用户数
            monthBefoeTime = monthBefoeTime.plusDays(1);
            //30总计
            turnover += businessDataVO.getTurnover();
            newUsers += businessDataVO.getNewUsers();
            validOrderCount += businessDataVO.getValidOrderCount();
        }
        //获取近一个月所有订单
        Integer totalOrderCount = orderMapper.selectByTimePerioc(time.minusDays(30),time);
        if (totalOrderCount != 0) {
            //计算订单完成率：有效订单/总订单数
            orderCompletionRate = validOrderCount / totalOrderCount.doubleValue();
        }
        if (validOrderCount != 0) {
            //平均客单价：营业额/有效订单
            unitPrice = turnover / validOrderCount;
        }
        //获得第4行
        XSSFRow row = sheetObj.getRow(3);
        //查询概览运营数据，提供给Excel模板文件
        BusinessDataVO businessData = workspaceService.businessData(time);
        //获取单元格
        row.getCell(2).setCellValue(turnover);
        row.getCell(4).setCellValue(orderCompletionRate);
        row.getCell(6).setCellValue(newUsers);
        row = sheetObj.getRow(4);
        row.getCell(2).setCellValue(validOrderCount);
        row.getCell(4).setCellValue(unitPrice);
        //6. 将构建的excel数据，通过输出流   将excel下载到客户端浏览器
        excelObj.write(response.getOutputStream());
        //7. 释放资源
        excelObj.close();
        System.out.println("excel数据写入完毕2");
    }
}

